﻿using MediatR;
using S.Archives.Domain.S.ArchivesDto.Total;
using S.Archives.Infrstructre;
using S.Archives.Read.Api.Application.Command.Total;

namespace S.Archives.Read.Api.Application.CommandHandlers.Total
{
    /// <summary>
    /// 存栏趋势
    /// </summary>
    public class SaveColumnTrendCommandHandler : IRequestHandler<SaveColumnTrendCommand, List<Nearly30DaysCloumnDto>>
    {
   private readonly DbHelper dbHelper;
   public SaveColumnTrendCommandHandler(DbHelper dbHelper)
   {
  this.dbHelper = dbHelper;
   }

   public async Task<List<Nearly30DaysCloumnDto>> Handle(SaveColumnTrendCommand request, CancellationToken cancellationToken)
   {
            //var sql = "SELECT     ShiftToColumnManagementId,    fi.ColumnName,   CAST(FORMAT(SurrenderDate, 'yyyy-MM-dd') AS DATE) as FormattedSurrenderDate,  count(sh.SheepRescissionId) as Count FROM [dbo].[SheepRescission] sh  JOIN FieldModel fi ON sh.ShiftToColumnManagementId = fi.ColumnManagementId  WHERE SurrenderDate BETWEEN DATEADD(DAY, -30, GETDATE()) AND GETDATE() GROUP BY ShiftToColumnManagementId, fi.ColumnName, CAST(FORMAT(SurrenderDate, 'yyyy-MM-dd') AS DATE) ORDER BY FormattedSurrenderDate";
            //var list= await dbHelper.GetListDataBySql<SaveColumnTrendDto>(sql);
            //var date = list.GroupBy(x => x.FormattedSurrenderDate).Select(g => new Nearly30DaysCloumnDto
            //{
            //    CloumDate = g.Key,
            //    CloumCount = g.Sum(item => item.Count)
            //}).ToList();
            //return date;
            var sql = "  WITH DateSeries AS ( SELECT TOP 30    CAST(DATEADD(DAY, 1 - ROW_NUMBER() OVER (ORDER BY s.object_id), GETDATE()) AS DATE) AS SeriesDate FROM sys.objects s  )    , AccumulatedData AS ( SELECT      d.SeriesDate AS date,     (    SELECT SUM(cnt)    FROM (   SELECT COUNT(ShiftToBuild) AS cnt   FROM SheepRescission sh   JOIN ColumnManagementModel co ON sh.ShiftToBuild = co.BuildingManagementId   WHERE CONVERT(date, SurrenderDate) <= d.SeriesDate   GROUP BY CONVERT(date, SurrenderDate), BuildingName    ) AS subquery1     ) AS total_in,     (    SELECT SUM(OutTotal)    FROM (   SELECT COUNT(TurnOutBuild) AS OutTotal   FROM SheepRescission sh   JOIN ColumnManagementModel co ON sh.TurnOutBuild = co.BuildingManagementId   WHERE CONVERT(date, SurrenderDate) <= d.SeriesDate   GROUP BY CONVERT(date, SurrenderDate), BuildingName    ) AS subquery2     ) AS total_out FROM DateSeries d  )    SELECT  date as CloumDate,  ISNULL(total_in, 0) - ISNULL(total_out, 0) AS CloumCount  FROM AccumulatedData  ORDER BY date;";
            var data = dbHelper.GetListDataBySql<Nearly30DaysCloumnDto>(sql);
            return await data;
   }
    }
}
